package com.pignest.hotel.dao.hibernate;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.pignest.framework.dao.BaseHibernateDao;
import com.pignest.hotel.beans.Living;
import com.pignest.hotel.beans.LivingSpecifics;
import com.pignest.hotel.beans.Member;
import com.pignest.hotel.beans.Room;
import com.pignest.hotel.beans.TbAccounts;
import com.pignest.hotel.beans.TbGoods;
import com.pignest.hotel.dao.LivingDao;
import com.pignest.hotel.definition.InComeItemType;
import com.pignest.hotel.definition.LivingState;

@SuppressWarnings("unchecked")
public class LivingHibernateDao extends BaseHibernateDao implements LivingDao {

	@Override
	public Living getLivingByRoom(Room room) {
		String hql = "from Living as l where l.room=? and (l.liveState=? or l.liveState=?)";
		List<Living> r = getHibernateTemplate().find(hql, new Object[] {room, LivingState.LIVING.name(), LivingState.PARTCHECKOUT.name()});
		if (r == null || r.isEmpty())
			return null;
		return r.get(0);
	}
	public List<Living> getLivingListByRoom(Room room){
		String hql = "from Living as l where l.room=? and (l.liveState=? or l.liveState=?)";
		List<Living> r = getHibernateTemplate().find(hql, new Object[] {room, LivingState.LIVING.name(), LivingState.PARTCHECKOUT.name()});
		if (r == null || r.isEmpty())
			return null;
		return r;
	}
	public Living getLivingByMember(Member M)
	{
		String hql = "from Living as l where l.member=? and l.liveState!=? and l.leaveDate is null";
		List<Living> r = getHibernateTemplate().find(hql, new Object[] {M, LivingState.CHECKOUT.name()});
		if (r == null || r.isEmpty())
			return null;
		return r.get(0);
	}
	public List<Living> queryLivingNotPage(final String sCustormName,final String sLiveState,
			final String sRoomName,final String sRoomType,final String sSex,final Date startLivingDate,final Date endLivingDate,final String oType)
	{
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from Living as b where 1=1");
				if(sCustormName!=null&&!sCustormName.equals(""))
					hql.append(" and b.custormName like :CustormName");
				if(sLiveState!=null&&!sLiveState.equals(""))
					hql.append(" and b.liveState =:LivieState");
				if(sRoomName!=null&&!sRoomName.equals(""))
					hql.append(" and b.room.name =:RoomName");
				if(sRoomType!=null&&!sRoomType.equals(""))
					hql.append(" and b.room.type.id=:RoomType");
				if(sSex!=null&&!sSex.equals(""))
					hql.append(" and b.sex=:Sex");
				if (startLivingDate != null)
					hql.append(" and b.liveTime>=:StateLiveTime");
	            if(endLivingDate!=null)
	            	hql.append(" and b.liveTime<=:EndLivingTime");
	            if(oType!=null&&!oType.equals(""))
	            	hql.append(" and b.openRoomType=:Otype");
				hql.append(" order by b.liveTime desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				if(sCustormName!=null&&!sCustormName.equals(""))
					query.setString("CustormName", '%' + sCustormName.replaceAll("%", "%%") + '%');
				if(sLiveState!=null&&!sLiveState.equals(""))
					query.setString("LivieState", sLiveState);
				if(sRoomName!=null&&!sRoomName.equals(""))
					query.setString("RoomName", sRoomName);
				if(sRoomType!=null&&!sRoomType.equals(""))
					query.setString("RoomType", sRoomType);
				if(sSex!=null&&!sSex.equals(""))
					query.setString("Sex", sSex);
				if (startLivingDate != null)
					query.setDate("StateLiveTime", startLivingDate);
				if (endLivingDate != null)
					query.setDate("EndLivingTime", endLivingDate);
				if(oType!=null&&!oType.equals(""))
					query.setString("Otype", oType);
				List<Living> list = query.list();
				return list;
			}
		});
		return list;
	}
	/**
	 * 查询入住并分页
	 * **/
	public List <Living> queryPageHQL(final String sCustormName,final String sLiveState,
			final String sRoomName,final String sRoomType,final String sSex,final Date startLivingDate,final Date endLivingDate,final String oType,
			final int start,final int length){
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from Living as b where 1=1");
				if(sCustormName!=null&&!sCustormName.equals(""))
					hql.append(" and b.custormName like :CustormName");
				if(sLiveState!=null&&!sLiveState.equals(""))
					hql.append(" and b.liveState =:LivieState");
				if(sRoomName!=null&&!sRoomName.equals(""))
					hql.append(" and b.room.name =:RoomName");
				if(sRoomType!=null&&!sRoomType.equals(""))
					hql.append(" and b.room.type.id=:RoomType");
				if(sSex!=null&&!sSex.equals(""))
					hql.append(" and b.sex=:Sex");
				if (startLivingDate != null)
					hql.append(" and b.liveTime>=:StateLiveTime");
	            if(endLivingDate!=null)
	            	hql.append(" and b.liveTime<=:EndLivingTime");
	            if(oType!=null&&!oType.equals(""))
	            	hql.append(" and b.openRoomType=:Otype");
	            hql.append(" order by b.liveTime desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				if(sCustormName!=null&&!sCustormName.equals(""))
					query.setString("CustormName", '%' + sCustormName.replaceAll("%", "%%") + '%');
				if(sLiveState!=null&&!sLiveState.equals(""))
					query.setString("LivieState", sLiveState);
				if(sRoomName!=null&&!sRoomName.equals(""))
					query.setString("RoomName", sRoomName);
				if(sRoomType!=null&&!sRoomType.equals(""))
					query.setString("RoomType", sRoomType);
				if(sSex!=null&&!sSex.equals(""))
					query.setString("Sex", sSex);
				if (startLivingDate != null)
					query.setDate("StateLiveTime", startLivingDate);
				if (endLivingDate != null)
					query.setDate("EndLivingTime", endLivingDate);
				if(oType!=null&&!oType.equals(""))
					query.setString("Otype", oType);
				query.setFirstResult(start);
				query.setMaxResults(length);
				List<Living> list = query.list();
				return list;
			}
		});
		return list;
	}
	/**计算当前入住余额,即要结算的金额
	 * 当前数据库值正数为客户充值负数为消费的
	 * sum()则为结算的金额
	 * **/
	@Deprecated
	public int queryConsumption(String livingId){
		int i=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(price) from TbAccounts b where b.living.id='"+livingId+"'");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.endstate='否'");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
	  	  Object s= r.get(0);
	  	  if(s!=null)
	  		 i=Integer.valueOf(s.toString());
	    }
	  return i;
	}
	/**计算个人房间入住消费金额
	 * @param isPositive true 返回为正数 false 返回负数
	 * **/
	@Deprecated
	public int querySingleConsum(String livingId,boolean isPositive){
		int i=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(price) from TbAccounts b where b.living.id='"+livingId+"'");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.endstate='否'");
		hql.append(" and b.price<0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
	  	  Object s= r.get(0);
	  	  if(s!=null)
	  		 i=Integer.valueOf(s.toString());
	    }
		if(isPositive){
			return -i;
		}
	  return i;
	}
	/**计算个人结账时
	 * 房间入住消费金额
	 * @param isPositive true 返回为正数 false 返回负数
	 * **/
	@Deprecated
	public int queryCheckSingleConsum(String livingId,boolean isPositive){
		int i=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(price) from TbAccounts b where b.living.id='"+livingId+"'");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.endstate='是'");
		hql.append(" and b.price<0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
	  	  Object s= r.get(0);
	  	  if(s!=null)
	  		 i=Integer.valueOf(s.toString());
	    }
		if(isPositive){
			return -i;
		}
	  return i;
	}
	/**计算当前多入住余额,即要结算的金额
	 * 当前数据库值正数为客户充值负数为消费的
	 * sum()则为结算的金额
	 * **/
	@Deprecated
	public int queryAllConsum(List<Living> list){
		int consum=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(list!=null&&list.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:list){
			if(i==list.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(list!=null&&list.size()!=0)
			hql.append(")");
		hql.append(" and b.endstate='否'");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
	  	  Object s= r.get(0);
	  	  if(s!=null)
	  		consum=Integer.valueOf(s.toString());
	    }
	  return consum;
	}


	/**入住下的所有账务包括押金等*/
	public List<TbAccounts> queryAccountsList(Living l)
	{
		StringBuilder hql = new StringBuilder();
		hql.append(" from TbAccounts b where 1=1");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
    	hql.append(" and b.living.id='").append(l.getId()).append("'");
    	List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
    	if (aclist == null || aclist.isEmpty())
			return null;
		return aclist;
	}
	/**查询不包括押金、续存的消费记录*/
	public List<TbAccounts> queryAccountsNotDeposit(Living l)
	{
		StringBuilder hql = new StringBuilder();
		hql.append(" from TbAccounts b where 1=1 ").append(" and b.living.id='").append(l.getId()).append("'");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
    	hql.append(" and b.type!='").append(InComeItemType.YJ.name()).append("'");
    	hql.append(" and b.type!='").append(InComeItemType.XCK.name()).append("'");
		List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
    	if (aclist == null || aclist.isEmpty())
			return null;
		return aclist;
	}
	/**
	 * 查询单个入住的押金
	 * **/
	@Deprecated
	public int querySingleDeposit(Living l)
	{
		int deposit =0;
		StringBuilder hql = new StringBuilder();
		hql.append(" from TbAccounts b where 1=1 ").append(" and b.living.id='").append(l.getId()).append("'");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
    	hql.append(" and b.type='").append(InComeItemType.YJ.name()).append("'");
		List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
		if(aclist!=null&&aclist.size()!=0){
		  	 TbAccounts ts =aclist.get(0);
		  	  if(ts!=null)
		  		deposit=ts.getPrice();
		    }
		return deposit;
	}
	/**
	 * 查询已经结账的消费
	 * @param 是否为正数返回
	 * 移动到incomeHibernateDao
	 * */
	@Deprecated 
	public int queryPayed(Living l,boolean isPositive ){
		int payed=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 ").append(" and b.living.id='").append(l.getId()).append("'");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.endstate='是'");
		hql.append(" and b.price<0");
		List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
		if(aclist!=null&&aclist.size()!=0){
			Object s= aclist.get(0);
		  	if(s!=null)
		  		payed=Integer.valueOf(s.toString());
		}
		if(isPositive&&payed<0)
			return -payed;
		return payed;
	}
	/**
	 * 根据物品的类别查询物品
	 * @param type '消费'、'借用'
	 * @author zhugb
	 * **/
	public List<TbGoods> queryGoods(String type)
	{
		StringBuilder hql = new StringBuilder();
		hql.append(" from TbGoods b  where 1=1 ");
		if(type!=null&&!type.equals(""))
			hql.append(" and b.type='").append(type).append("'");
		List<TbGoods> list=getHibernateTemplate().find(hql.toString());
    	if (list == null || list.isEmpty())
			return null;
		return list;
	}
	public LivingSpecifics querySpecifices(String livingId,String type)
	{

		StringBuffer hql = new StringBuffer();
		hql.append(" from LivingSpecifics b where b.living.id='").append(livingId).append("'");
		hql.append(" and b.operateType='").append(type).append("'");
		List<LivingSpecifics> r = getHibernateTemplate().find(hql.toString());
		if (r == null || r.isEmpty())
			return null;
		return r.get(0);
	}
    public List<Living> queryTeamLiving(String serialNumber,String livingId){
    	StringBuilder hql = new StringBuilder();
		hql.append(" from Living b  where 1=1 ");
		if(serialNumber!=null)
	       hql.append(" and b.serialNumber='").append(serialNumber).append("'");
		if(livingId!=null)
			hql.append(" and b.id='").append(livingId).append("'");
		List<Living> list=getHibernateTemplate().find(hql.toString());
    	if (list == null || list.isEmpty())
			return null;
		return list;
    }
	
	public List<Living> listCurrentBasicLivings() {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
//		    	return super.query(Living.class, "liveState in (?, ?)", new Object[] {LivingState.LIVING.name(), LivingState.PARTCHECKOUT.name()}, null);
				String hql = "select l.room.id, l.openRoomType, l.overtime, l.custormName from Living l where liveState in(:state1, :state2)";
				Query q = session.createQuery(hql);
				q.setParameter("state1", LivingState.LIVING.name());
				q.setParameter("state2", LivingState.PARTCHECKOUT.name());
				List<Object> r = q.list();
				List<Living> livings = new ArrayList<Living>();
				for (Object object : r) {
					Object[] o = (Object[])object;
					String roomId = (String)o[0];
					String openType = (String)o[1];
					Integer overtime = (Integer)o[2];
					String custorm = (String)o[3];
					Living l = new Living();
					l.setRoomId(roomId);
					l.setOpenRoomType(openType);
					l.setOvertime(overtime);
					l.setCustormName(custorm);
					livings.add(l);
				}
				return livings;
			}
		});
	}

}
